
#############################################################################
# $Id: HotelUtils.pm 25141 2008-08-19 15:38:16Z asawczyn $
#############################################################################

package RepositoryTestUtils;

use strict;
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);

require Exporter;

@ISA = qw(Exporter);

@EXPORT = qw(
);

@EXPORT_OK = qw(
   create_table_test_person
   drop_table_test_person
   populate_table_test_person
   create_table_test_app_cache
   drop_table_test_app_cache
   create_table
   drop_table
   populate_table
);

use App;
use App::Repository;

my $dbtype = $App::options{dbtype} || "mysql";

sub create_table_test_person {
    &App::sub_entry if ($App::trace);
    my ($rep) = @_;
    my $dbh = $rep->{dbh};

    my ($ddl, $rc);

    my $autoincrement = "";
    my $suffix = "";
    if ($dbtype eq "mysql") {
        $autoincrement = " auto_increment";
        $suffix = " ENGINE=InnoDB DEFAULT CHARSET=latin1";
    }

    my $double_type   = ($dbtype eq "mysql") ? 'double' : 'float';
    my $datetime_type = ($dbtype eq "mysql") ? 'datetime' : 'date';

    $ddl = <<EOF;
create table test_person (
    person_id      integer         not null$autoincrement,
    first_name     varchar(99)     null,
    last_name      varchar(99)     null,
    address        varchar(99)     null,
    city           varchar(99)     null,
    state          varchar(99)     null,
    zip            varchar(10)     null,
    country        char(2)         null,
    city_cd        varchar(5)      null,
    home_phone     varchar(99)     null,
    work_phone     varchar(99)     null,
    email_address  varchar(99)     null,
    gender         char(1)         null,
    birth_dt       date            null,
    age            integer         null,
    data           blob            null,
    chess_rating   float           null,
    modify_dttm    timestamp       null,
    change_dttm    $datetime_type  null,
    deci_col       decimal         null,
    double_col     $double_type    null,
    long_col       long varchar    null,
    primary key (person_id)
)$suffix
EOF
    print "DEBUG:\n$ddl\n" if ($App::options{debug_sql});
    $rc = $dbh->do($ddl);
    print "DEBUG: rc=[$rc] errstr=[$DBI::errstr]\n" if ($App::options{debug_sql});

    $ddl = "create unique index test_person_ak1 on test_person (first_name, state, gender)";
    print "DEBUG:\n$ddl\n" if ($App::options{debug_sql});
    $rc = $dbh->do($ddl);
    print "DEBUG: rc=[$rc] errstr=[$DBI::errstr]\n" if ($App::options{debug_sql});

    $ddl = "create index test_person_ie1 on test_person (last_name, first_name)";
    $dbh->do($ddl);
    if ($dbtype eq "oracle") {
        $ddl = <<EOF;
create sequence test_person_seq start with 1 increment by 1 nomaxvalue nocycle cache 200
EOF
        $dbh->do($ddl);
        $ddl = <<EOF;
CREATE OR REPLACE TRIGGER tib_test_person
BEFORE INSERT ON test_person
FOR EACH ROW
WHEN (new.person_id IS NULL or new.person_id = 0)
BEGIN
    SELECT test_person_seq.NEXTVAL
      INTO :new.person_id
      FROM DUAL;
END tib_test_person;
EOF
        $dbh->do($ddl);
    }

    &App::sub_exit() if ($App::trace);
}

sub drop_table_test_person {
    &App::sub_entry if ($App::trace);
    my ($rep) = @_;
    my $dbh = $rep->{dbh};

    print "DEBUG:\ndrop table test_person\n" if ($App::options{debug_sql});
    my ($rc);
    eval { $dbh->do("drop table test_person"); };
    print "DEBUG: rc=[$rc] errstr=[$DBI::errstr]\n" if ($App::options{debug_sql});
    #warn $@ if ($@);
    if ($dbtype eq "oracle") {
        eval { $dbh->do("drop sequence test_person_seq"); };
    }

    &App::sub_exit() if ($App::trace);
}

sub populate_table_test_person {
    &App::sub_entry if ($App::trace);
    my ($rep) = @_;
    my $dbh = $rep->{dbh};
    $dbh->do("insert into test_person (age,first_name,gender,state) values (39,'stephen',  'M','GA')");
    $dbh->do("insert into test_person (age,first_name,gender,state) values (37,'susan',    'F','GA')");
    $dbh->do("insert into test_person (age,first_name,gender,state) values ( 6,'maryalice','F','GA')");
    $dbh->do("insert into test_person (age,first_name,gender,state) values ( 3,'paul',     'M','GA')");
    $dbh->do("insert into test_person (age,first_name,gender,state) values ( 1,'christine','F','GA')");
    $dbh->do("insert into test_person (age,first_name,gender,state) values (45,'tim',      'M','GA')");
    $dbh->do("insert into test_person (age,first_name,gender,state) values (39,'keith',    'M','GA')");
    &App::sub_exit() if ($App::trace);
}

sub create_table_test_app_cache {
    my ($rep) = @_;
    my $dbh = $rep->{dbh};

    my $suffix = "";
    my $CURRENT_TIMESTAMP = "";
    my $datetime = "date";
    my $longblob = "blob";
    if ($dbtype eq "mysql") {
        $suffix = "ENGINE=InnoDB DEFAULT CHARSET=latin1";
        $CURRENT_TIMESTAMP = "default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP";
        $datetime = "datetime";
        $longblob = "longblob";
    }

    eval { $dbh->do("drop table test_app_cache"); };
    my $ddl = <<EOF;
create table test_app_cache (
    cache_type         varchar(16) not null,
    cache_key          varchar(40) not null,
    generate_dttm      $datetime    default null,
    serializer         varchar(12) default null,
    serialization_args varchar(64) default null,
    data               $longblob,
    modify_dttm        timestamp  not null $CURRENT_TIMESTAMP,
    primary key        (cache_type,cache_key)
) $suffix
EOF
    $dbh->do($ddl);
    $ddl = "create index test_app_cache_ie1 on test_app_cache (modify_dttm)";
    $dbh->do($ddl);

    if ($dbtype eq 'oracle') {
        $ddl = <<EOF;
CREATE OR REPLACE TRIGGER tib_test_app_cache
BEFORE INSERT ON test_app_cache
FOR EACH ROW
WHEN (new.modify_dttm IS NULL)
BEGIN
    SELECT sysdate
      INTO :new.modify_dttm
      FROM DUAL;
END tib_test_app_cache;
EOF
        $dbh->do($ddl);
    }
}

sub drop_table_test_app_cache {
    &App::sub_entry if ($App::trace);
    my ($rep) = @_;
    my $dbh = $rep->{dbh};
    eval { $dbh->do("drop table test_app_cache"); };
    &App::sub_exit() if ($App::trace);
}

######################################################################################################
# GENERIC DATABASE TABLE GENERATION
######################################################################################################

my (%table_schema, %table_index, %table_autoid_column, %table_data);

$table_schema{test_person} = <<EOF;
create table test_person (
    person_id      integer         not null AUTOINCREMENT,
    first_name     varchar(99)     null,
    last_name      varchar(99)     null,
    address        varchar(99)     null,
    city           varchar(99)     null,
    state          varchar(99)     null,
    zip            varchar(10)     null,
    country     char(2)         null,
    city_cd        varchar(5)      null,
    home_phone     varchar(99)     null,
    work_phone     varchar(99)     null,
    email_address  varchar(99)     null,
    gender         char(1)         null,
    birth_dt       date            null,
    age            integer         null,
    data           blob            null,
    chess_rating   float           null,
    modify_dttm    timestamp       null,
    change_dttm    DATETIME        null,
    deci_col       decimal         null,
    double_col     DOUBLE          null,
    long_col       long varchar    null,
    primary key (person_id)
)SUFFIX
EOF
$table_index{test_person} = [
    "create unique index test_person_ak1 on test_person (first_name, state, gender)",
    "create index test_person_ie1 on test_person (last_name, first_name)",
];
$table_autoid_column{test_person} = "person_id";
$table_data{test_person} = [
    "insert into test_person (age,first_name,gender,state,country) values (39,'stephen',  'M','GA','US')",
    "insert into test_person (age,first_name,gender,state,country) values (37,'susan',    'F','GA','US')",
    "insert into test_person (age,first_name,gender,state,country) values ( 6,'maryalice','F','GA','US')",
    "insert into test_person (age,first_name,gender,state,country) values ( 3,'paul',     'M','GA','US')",
    "insert into test_person (age,first_name,gender,state,country) values ( 1,'christine','F','GA','US')",
    "insert into test_person (age,first_name,gender,state,country) values (45,'tim',      'M','GA','US')",
    "insert into test_person (age,first_name,gender,state,country) values (39,'keith',    'M','GA','US')",
];

$table_schema{test_country} = <<EOF;
create table test_country (
    country_id             integer      not null AUTOINCREMENT,
    country                char(2)      not null,
    country_nm             varchar(64)  not null,
    primary key (country_id)
)SUFFIX
EOF
$table_index{test_country} = [
    "create index test_country_ie1 on test_country (country)",
    "create index test_country_ie2 on test_country (country_nm)",
];
$table_autoid_column{test_country} = "country_id";
$table_data{test_country} = [
    "insert into test_country (country, country_nm) values ('AU','AUSTRALIA')",
    "insert into test_country (country, country_nm) values ('BR','BRAZIL')",
    "insert into test_country (country, country_nm) values ('CN','CHINA')",
    "insert into test_country (country, country_nm) values ('DE','GERMANY')",
    "insert into test_country (country, country_nm) values ('ES','SPAIN')",
    "insert into test_country (country, country_nm) values ('FR','FRANCE')",
    "insert into test_country (country, country_nm) values ('GB','UNITED KINGDOM')",
    "insert into test_country (country, country_nm) values ('IT','ITALY')",
    "insert into test_country (country, country_nm) values ('JP','JAPAN')",
    "insert into test_country (country, country_nm) values ('MX','MEXICO')",
    "insert into test_country (country, country_nm) values ('RU','RUSSIAN FEDERATION')",
    "insert into test_country (country, country_nm) values ('US','UNITED STATES')",
    "insert into test_country (country, country_nm) values ('ZA','SOUTH AFRICA')",
];

$table_schema{test_city} = <<EOF;
create table test_city (
    city_cd             char(3)      not null,
    state               char(2)      null,
    country             char(2)      null,
    city_nm             varchar(99)  not null,
    arp_nm              varchar(99)  null,
    primary key (city_cd)
)SUFFIX
EOF
$table_index{test_city} = [
    "create index test_city_ie1 on test_city (city_nm)",
    "create index test_city_ie2 on test_city (state)",
    "create index test_city_ie3 on test_city (country)",
];
$table_data{test_city} = [
    "insert into test_city values ('ATL', 'GA', 'US', 'Atlanta',           'William B. Hartsfield International Airport')",
    "insert into test_city values ('CDG', '',   'FR', 'Paris',             'Roissy Ch. de Gaulle')",
    "insert into test_city values ('DFW', 'TX', 'US', 'Dallas/Fort Worth', 'Dallas/Fort Worth Int''l')",
    "insert into test_city values ('EWR', 'NJ', 'US', 'Newark',            'Newark International Airport')",
    "insert into test_city values ('FCO', '',   'IT', 'Roma',              'Leonardo da Vinci/Fiumicino')",
    "insert into test_city values ('HKG', '',   'CN', 'Hong Kong',         'Chek Lap Kok International Airport')",
    "insert into test_city values ('HND', '',   'JP', 'Tokyo',             'Haneda')",
    "insert into test_city values ('IAD', 'DC', 'US', 'Washington',        'Washington Dulles Int''l Airport')",
    "insert into test_city values ('JFK', 'NY', 'US', 'New York',          'John F. Kennedy International Airport')",
    "insert into test_city values ('JNB', '',   'ZA', 'Johannesburg',      'Johannesburg International Airport')",
    "insert into test_city values ('LAX', 'CA', 'US', 'Los Angeles',       'Los Angeles International Airport')",
    "insert into test_city values ('LGA', 'NY', 'US', 'New York',          'La Guardia Airport')",
    "insert into test_city values ('LGW', '',   'GB', 'London',            'Gatwick Airport')",
    "insert into test_city values ('MAD', '',   'ES', 'Madrid',            'Barajas')",
    "insert into test_city values ('MDW', 'IL', 'US', 'Chicago',           'Chicago Midway Airport')",
    "insert into test_city values ('MEX', '',   'MX', 'Mexico City',       'Benito Juarez International')",
    "insert into test_city values ('NRT', '',   'JP', 'Tokyo',             'Narita')",
    "insert into test_city values ('ORD', 'IL', 'US', 'Chicago',           'Chicago-O''Hare International Airport')",
    "insert into test_city values ('SDU', 'RJ', 'BR', 'Rio de Janeiro',    'Aeroporto Santos Dumont')",
    "insert into test_city values ('SVO', '',   'RU', 'Moscow',            'Sheremetyevo')",
    "insert into test_city values ('SXF', '',   'DE', 'Berlin',            'Schoenefeld')",
    "insert into test_city values ('SYD', '',   'AU', 'Sydney',            'Kingsford Smith')",
    "insert into test_city values ('THF', '',   'DE', 'Berlin',            'Tempelhof')",
    "insert into test_city values ('TXL', '',   'DE', 'Berlin',            'Tegel')",
];

$table_schema{test_visit} = <<EOF;
create table test_visit (
    city_cd             char(3)      not null,
    person_id           integer      not null,
    visit_dt            date         not null,
    occasion            varchar(99)  null,
    primary key (city_cd, person_id, visit_dt)
)SUFFIX
EOF

if ($dbtype eq "oracle") {
    $table_data{test_visit} = [
        "insert into test_visit values ('LAX', 1, to_date('1962-11-09','YYYY-MM-DD'), 'Born')",
        "insert into test_visit values ('BOS', 1, to_date('1980-08-30','YYYY-MM-DD'), 'College')",
        "insert into test_visit values ('JNB', 1, to_date('1986-10-20','YYYY-MM-DD'), 'Mission Trip')",
        "insert into test_visit values ('WAS', 1, to_date('1991-08-20','YYYY-MM-DD'), 'Back to college')",
        "insert into test_visit values ('ATL', 1, to_date('1993-06-01','YYYY-MM-DD'), 'Move after graduation')",
    ];
}
else {
    $table_data{test_visit} = [
        "insert into test_visit values ('LAX', 1, '1962-11-09', 'Born')",
        "insert into test_visit values ('BOS', 1, '1980-08-30', 'College')",
        "insert into test_visit values ('JNB', 1, '1986-10-20', 'Mission Trip')",
        "insert into test_visit values ('WAS', 1, '1991-08-20', 'Back to college')",
        "insert into test_visit values ('ATL', 1, '1993-06-01', 'Move after graduation')",
    ];
}

$table_schema{test_hotel_prop} = <<EOF;
create table test_hotel_prop (
    prop_id             integer      not null AUTOINCREMENT,
    prop_cd             char(12)     not null,
    prop_nm             varchar(99)  not null,
    address             varchar(255) null,
    chain_cd            char(2)      null,
    state               char(2)      null,
    country             char(2)      null,
    primary key (prop_id)
)SUFFIX
EOF
$table_index{test_hotel_prop} = [
    "create unique index test_hotel_prop_ak1 on test_hotel_prop (prop_cd)",
];
$table_autoid_column{test_hotel_prop} = "prop_id";
$table_data{test_hotel_prop} = [
    "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('9128' ,'Hilton New York'               ,'1335 Avenue Of The Americas, New York, NY 10019','HH','NY','US')",
    "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('13111','Marriott Marquis NYC'          ,'1535 BROADWAY, New York, NY 10036'              ,'MC','NY','US')",
    "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('668'  ,'Sheraton New York Hotel&Towers','811 7th Ave, New York, NY 10019'                ,'SI','NY','US')",
    "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('3391' ,'Crowne Plaza Time Sq Manhattan','1605 Broadway, Manhattan, NY 10019'             ,'CP','NY','US')",
    "insert into test_hotel_prop (prop_cd,prop_nm,address,chain_cd,state,country) values ('962'  ,'Grand Hyatt New York'          ,'Park Ave At Grand Central, New York, NY 10017'  ,'HY','NY','US')",
];

$table_schema{test_hotel_bkg} = <<EOF;
create table test_hotel_bkg (
    bkg_id             integer      not null AUTOINCREMENT,
    conf_num           varchar(24)  not null,
    bkg_dt             date         not null,
    person_id          integer      not null,
    prop_id            integer      not null,
    arv_dt             date         not null,
    dpt_dt             date         not null,
    cancel_dt          date         null,
    rooms              integer      not null,
    rev_amt_usd        float        not null,
    primary key (bkg_id)
)SUFFIX
EOF
$table_index{test_hotel_bkg} = [
    "create unique index test_hotel_bkg_ak1 on test_hotel_bkg (conf_num, bkg_dt)",
];
$table_autoid_column{test_hotel_bkg} = "bkg_id";
$table_data{test_hotel_bkg} = [
    "insert into test_hotel_bkg values (null,'AAA001','2008-11-01',1,1,'2008-12-01','2008-12-04',null,1,300)",
];

sub create_table {
    &App::sub_entry if ($App::trace);
    my ($rep, $table) = @_;
    my $dbh = $rep->{dbh};

    my $ddl = $table_schema{$table} || die "Schema not defined for table [$table]\n";

    my $autoincrement = "";
    my $suffix = "";
    if ($dbtype eq "mysql") {
        $autoincrement = " auto_increment";
        $suffix = " ENGINE=InnoDB DEFAULT CHARSET=latin1";
    }

    my $double_type   = ($dbtype eq "mysql") ? 'double' : 'float';
    my $datetime_type = ($dbtype eq "mysql") ? 'datetime' : 'date';

    $ddl =~ s/AUTOINCREMENT/$autoincrement/g;
    $ddl =~ s/SUFFIX/$suffix/g;
    $ddl =~ s/DOUBLE/$double_type/g;
    $ddl =~ s/DATETIME/$datetime_type/g;
    if ($dbtype eq "oracle") {
        $ddl =~ s/varchar\(/varchar2\(/ig;
    }
    print "DEBUG:\n$ddl\n" if ($App::options{debug_sql});
    my $rc = $dbh->do($ddl);
    print "DEBUG: rc=[$rc] errstr=[$DBI::errstr]\n" if ($App::options{debug_sql});

    my $ddls = $table_index{$table};
    if ($ddls) {
        foreach $ddl (@$ddls) {
            $dbh->do($ddl);
        }
    }

    my ($autoid_column);
    if ($dbtype eq "oracle") {
        $autoid_column = $table_autoid_column{$table};
        if ($autoid_column) {
            $ddl = <<EOF;
create sequence ${table}_seq start with 1 increment by 1 nomaxvalue nocycle cache 200
EOF
            $dbh->do($ddl);
            $ddl = <<EOF;
CREATE OR REPLACE TRIGGER tib_$table
BEFORE INSERT ON $table
FOR EACH ROW
WHEN (new.$autoid_column IS NULL or new.$autoid_column = 0)
BEGIN
    SELECT ${table}_seq.NEXTVAL
      INTO :new.$autoid_column
      FROM DUAL;
END tib_${table};
EOF
            $dbh->do($ddl);
        }
    }

    &App::sub_exit() if ($App::trace);
}

sub drop_table {
    &App::sub_entry if ($App::trace);
    my ($rep, $table) = @_;
    my $dbh = $rep->{dbh};

    print "DEBUG:\ndrop table $table\n" if ($App::options{debug_sql});
    my ($rc);
    eval { $rc = $dbh->do("drop table $table"); };
    print "DEBUG: rc=[$rc] errstr=[$DBI::errstr]\n" if ($App::options{debug_sql});
    if ($dbtype eq "oracle") {
        eval { $dbh->do("drop sequence ${table}_seq"); };
    }

    &App::sub_exit() if ($App::trace);
}

sub populate_table {
    &App::sub_entry if ($App::trace);
    my ($rep, $table) = @_;
    my $dbh = $rep->{dbh};
    my $data = $table_data{$table};
    if ($data) {
        foreach my $sql (@$data) {
            $dbh->do($sql);
        }
    }
    &App::sub_exit() if ($App::trace);
}

1;
